package com.intelligentmeterreadingsys.report.db;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.intelligentmeterreadingsys.BaseDao.BaseDao;
import com.intelligentmeterreadingsys.povo.EngineeringOrderPoVo;
import com.intelligentmeterreadingsys.povo.ResourcesPoVo;
import com.intelligentmeterreadingsys.povo.TypePoVo;
import com.intelligentmeterreadingsys.povo.UserWaterVO;

import dbhelper.DbTableName;

/**
 * 工程单增、删除、改、查询
 * 
 * @author king
 * 
 */
public class ReportDao extends BaseDao {

	public SQLiteDatabase db;

	public ReportDao(Context context) {
		super(context);
		this.db = super.getDb();
	}

	/**
	 * 添加工程单
	 * 
	 * @param values
	 * @return
	 */
	public int insertReport(ContentValues values,String ordertype) {
       int id=0;
        String sql1="select number from type where name='"+ordertype+"' and status='2'";
        Cursor cursor1=db.rawQuery(sql1, null);
        String orderid="";
        if(cursor1.moveToNext()){
        	orderid=cursor1.getString(cursor1.getColumnIndex("number"));
        	
        }
        if(cursor1!=null){
			cursor1.close();
		}
        values.put("ordertype", orderid);
		long rowid=db.insert(DbTableName.ENGINEERINGORDER, null, values);
		
		if(rowid>0){
			String sql="select max(_id) as max_id from engineeringOrder";
			Cursor cursor=db.rawQuery(sql, null);
			if(cursor.moveToNext()){
				id=cursor.getInt(cursor.getColumnIndex("max_id"));
			}
			
		
			if(cursor!=null){
				cursor.close();
			}
			
		}else{
			return 0;
		}
	
		return id;

	}

	/**
	 * 查询工程单类型
	 * 
	 * @return
	 */
	public List<TypePoVo> queryOrderType() {
		List<TypePoVo> list = new ArrayList<TypePoVo>();
		String sql = "select * from type where status='2'";
		Cursor cursor = db.rawQuery(sql, null);

		if (cursor == null) {
			
			return null;
		}
		while (cursor.moveToNext()) {
			TypePoVo type = new TypePoVo();
			type.setName(cursor.getString(cursor.getColumnIndex("name")));
			type.setNumber(cursor.getString(cursor.getColumnIndex("number")));
			System.out.println(cursor.getString(cursor.getColumnIndex("number")));
			list.add(type);
		}
		
		if(cursor!=null){
			cursor.close();
		}
		return list;
	}

	/**
	 * 修改工程单
	 * 
	 * @param value
	 * @return
	 */
	public boolean modifyOrder(ContentValues value, String id,String type) {
		boolean b = false;
        db.delete(DbTableName.RESOURCES, "otherid=? and status=?", new String[]{id,"1"});
		String sql="select number from type where name='"+type+"' and status='2'";
	    Cursor cursor=db.rawQuery(sql, null);
	    String ordertype="";
	    if(cursor.moveToNext()){
	    	ordertype=cursor.getString(cursor.getColumnIndex("number"));
	    }
	    value.put("ordertype", ordertype);
	    if(cursor!=null){
	    	cursor.close();
	    }
		int i = db.update(DbTableName.ENGINEERINGORDER, value, "_id=?",
				new String[] { id });

		if (i > 0) {
			b = true;
		}

		return b;
	}

	
	/**
	 * 查询工程单
	 * @param number水表编号
	 * @return
	 */
	public List<EngineeringOrderPoVo> queryOrder(String orderid){
		
		List<EngineeringOrderPoVo> list=new ArrayList<EngineeringOrderPoVo>();
		
		   String sql="select e.[_id],e.[status],e.[waternumber],e.[ordertype],e.[ordername],e.[desc],e.[imagepath],e.[videopath]," +
		   		"e.[soundpath],t.[name] from engineeringorder e,type t where e.[ordertype]=t.[number] and t.[status]='2'";
		   
		   if(orderid!=null){
			   sql=sql+" and  e.[_id]='"+orderid+"'";
		   }
		   sql=sql+" order by e.[_id] desc";
		   System.out.println(sql);
		 Cursor cursor=  db.rawQuery(sql, null);
		 
		 while(cursor.moveToNext()){
			 EngineeringOrderPoVo order=new EngineeringOrderPoVo();
			 order.setDesc(cursor.getString(cursor.getColumnIndex("desc")));
			 order.setTypenumber(cursor.getString(cursor.getColumnIndex("ordertype")));
			 order.setName(cursor.getString(cursor.getColumnIndex("ordername")));
			 order.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));
			
			 order.setStatus(cursor.getString(cursor.getColumnIndex("status")));
			 order.setVideopath(cursor.getString(cursor.getColumnIndex("videopath")));
			 order.setSoundpath(cursor.getString(cursor.getColumnIndex("soundpath")));
			 order.setTypename(cursor.getString(cursor.getColumnIndex("name")));
			 order.setId(cursor.getString(cursor.getColumnIndex("_id")));
			 
			 list.add(order);
			 
		 }
		 if(cursor!=null){
			 cursor.close();
		 }
		
		return list;
		
	};
	
	/**
	 * 删除一个工程单
	 * @param id
	 * @return
	 */
	public boolean delectOrder(String id){
		
		return super.delect(id, DbTableName.ENGINEERINGORDER);
	}
	
	/**
	 * 通过名称查询工程类型
	 * @param name
	 * @return
	 */
	public String queryTypeID(String name){
		String id="";
		String sql="select * from type where status='2' and name='"+name+"'";
		
		Cursor cursor=db.rawQuery(sql, null);
		if(cursor.moveToNext()){
			id=cursor.getString(cursor.getColumnIndex("number"));
		}
		if(cursor!=null){
			cursor.close();
		}
		return id;
		
	}
	
	/**
	 * 通过RFID号找到水表编号
	 * @param rfid
	 * @return
	 */
	public String queryWaterNumber(String rfid){
		
		String sql="select waternumber from waterinfo where RFIDnumber='"+rfid+"' ";
		
		Cursor cursor=db.rawQuery(sql, null);
		String waternumber="";
		if(cursor.moveToNext()){
			waternumber=cursor.getString(cursor.getColumnIndex("waternumber"));
		}
		if(cursor!=null){
			cursor.close();
		}
		return waternumber;
		
	}
	
	/**
	 * 查询工程单总数据
	 * @return
	 */
	public int queryOrderTotal(){
		String sql="Select _id From engineeringOrder ";
		Cursor cursor=db.rawQuery(sql, null);
		int i=cursor.getCount();
		if(cursor!=null){
			cursor.close();
		}
		return i;
		
	}
	
	/**
	 * 查询未上传工程单数
	 * @return
	 */
	public int queryOrderUpload(){
		String sql="select _id from engineeringOrder where status='0'";
		Cursor cursor=db.rawQuery(sql, null);
		int i=cursor.getCount();
		if(cursor!=null){
			cursor.close();
		}
		return i;
		
	}
	
	/**
	 * 查询所需要上传的工程单
	 * @return
	 */
	public List<EngineeringOrderPoVo> queryUpload(String orderid){
	
		List<EngineeringOrderPoVo> list=new ArrayList<EngineeringOrderPoVo>();
		String sql="select * from engineeringorder e where e.[status]='0'";
		if(orderid!=null){
			sql=sql+" and e.[_id]='"+orderid+"'";
		}
		Cursor cursor=db.rawQuery(sql, null);
		Cursor cur=null;
		while(cursor.moveToNext()){
			EngineeringOrderPoVo order=new EngineeringOrderPoVo();
			order.setDesc(cursor.getString(cursor.getColumnIndex("desc")));
			order.setId(cursor.getString(cursor.getColumnIndex("_id")));
			order.setName(cursor.getString(cursor.getColumnIndex("ordername")));
			order.setSoundpath(cursor.getString(cursor.getColumnIndex("soundpath")));
			order.setTypenumber(cursor.getString(cursor.getColumnIndex("ordertype")));
			order.setWaternumber(cursor.getString(cursor.getColumnIndex("waternumber")));
			order.setVideopath(cursor.getString(cursor.getColumnIndex("videopath")));
			String reoouseSQL="select * from "+DbTableName.RESOURCES+ " where otherid='"+order.getId()+"' and type='1'" +
					"and status='1'";
			  cur=db.rawQuery(reoouseSQL, null);
			 List<String> listimg=new ArrayList<String>(); 
			 while(cur.moveToNext()){
				
				 listimg.add(cur.getString(cur.getColumnIndex("path")));
			 }
			 if(cur!=null){
					cur.close();
				}
			 order.setImagepath(listimg);
			list.add(order);
		}
		
		if(cursor!=null){
			cursor.close();
		}
	
		return list;
		
	}
	
	/**
	 * 上传成功后更新状态
	 * @param id
	 * @return
	 */
	public boolean modifyStatus(int id){
		ContentValues value=new ContentValues();
		value.put("status", "1");
		return super.modify("_id=?", DbTableName.ENGINEERINGORDER, value);
	}
	
	/**
	 * 删除图片路径
	 * @param otherid
	 * @return
	 */
	public boolean delectImg(String otherid){
		System.out.println("otherid------->"+otherid);
		boolean b=false;
		
		int id=db.delete(DbTableName.RESOURCES, "otherid=? and  status=?",new String[]{otherid,"1"} );
		if(id>0){
			b=true;
		}
		return b;
	}
	/**
	 * 添加资源（图片、视频、声音）
	 * @param list
	 * @return
	 */
	public boolean addResources(List<ContentValues> list){
		
		return super.insertALL(list, DbTableName.RESOURCES);
		
	}
	
	/**
	 * 查询工程单图片
	 * @param otherid
	 * @return
	 */
	public ArrayList<String> queryImagePath(String otherid){

		ArrayList<String> list=new ArrayList<String>();
		String sql="select * from "+DbTableName.RESOURCES+" where otherid='"+otherid+"' and type='1' " +
				"and status='1' order by _id desc";
		
		Cursor cursor=db.rawQuery(sql, null);
		while(cursor.moveToNext()){
			
			
			list.add(cursor.getString(cursor.getColumnIndex("path")));
		}
		if(cursor!=null){
			cursor.close();
		}
		return list;	
	}
	
	/**
	 * 通过RFID卡号查询用户及水表信息
	 * 
	 * @param RFID
	 * @return
	 */
	public List<UserWaterVO> queryUserWaterInfo(String RFID,String waterid) {
		List<UserWaterVO> list=new ArrayList<UserWaterVO>();
		UserWaterVO water = null;
		String sql = "select w.[_id] as waterid,w.[isupload],w.[thisdate],w.[desc],w.[thismonthwater], w.[waternumber],w.[usernumber],w.[wateradds], w.[lastmonthwater]"
				+ " ,w.[waterproperties],u.[phone],w.[metercaliber],u.[username],w.[alarmlimit],w.[lowalarmlimit] from waterinfo w,userinfo u where "
				+ "w.[usernumber]=u.[usernumber] ";
		if(RFID!=null){
			sql=sql+" and w.[RFIDnumber]='"+RFID+"'";
		}
		
		if(waterid!=null){
			sql=sql+" and w.[_id]="+waterid;
		}
		System.out.println("读卡SQL"+sql);

		Cursor cursor = db.rawQuery(sql, null);
		while (cursor.moveToNext()) {
			water = new UserWaterVO();
			water.setIsupload(cursor.getString(cursor.getColumnIndex("isupload")));
			water.setAdds(cursor.getString(cursor.getColumnIndex("wateradds")));
			water.setLastmonthwater(cursor.getDouble(cursor
					.getColumnIndex("lastmonthwater")));
			water.setMetercaliber(cursor.getString(cursor
					.getColumnIndex("metercaliber")));
			water.setName(cursor.getString(cursor.getColumnIndex("username")));
			water.setNumber(cursor.getString(cursor
					.getColumnIndex("usernumber")));
			water.setWaternumber(cursor.getString(cursor
					.getColumnIndex("waternumber")));
			water.setAlarmlimit(cursor.getDouble(cursor
					.getColumnIndex("alarmlimit")));
			water.setLowalarmlimit(cursor.getDouble(cursor
					.getColumnIndex("lowalarmlimit")));
			water.setWaterid(cursor.getString(cursor.getColumnIndex("waterid")));
			water.setThisdate(cursor.getString(cursor.getColumnIndex("thisdate")));
		   String thismonthwater=cursor.getString(cursor.getColumnIndex("thismonthwater"));
		   water.setDesc(cursor.getString(cursor.getColumnIndex("desc")));
		   if(thismonthwater!=null&&!thismonthwater.equals("")){
			   water.setThismonthwater(cursor.getDouble(cursor.getColumnIndex("thismonthwater")));
		   }else {
			   water.setThismonthwater(0.0);
		   }
		   water.setWaterproperties(cursor.getString(cursor.getColumnIndex("waterproperties")));
		   water.setPhone(cursor.getString(cursor.getColumnIndex("phone")));
             list.add(water);
		}
		if(cursor!=null){
			cursor.close();
		}
		return list;

	}

	public void close(){
		if(db!=null&&db.isOpen()){
			db.close();
			super.close();
		}
		}
}
